Oracle SQL练习之Sum,Count,Decode,Case…When的使用 | 您所在的位置:网站首页 › decode case when › Oracle SQL练习之Sum,Count,Decode,Case…When的使用 |
最近在做报表,写了一些SQL,整理一下,其中涉及到Oracle很多语法的使用,方便大家参考与复习! SELECT RESULT.SITE_ID,RESULT.SITE_NAME,RESULT.STATION_TYPE,RESULT.STATION_NAME,RESULT.MONITOR_TIME,RESULT.PROJECT_ID,RESULT.PROJECT_NAME,RESULT.MONITOR_RESULT,RESULT.IF_GUARD_LEVEL, --根据水情站点类型和监测指标统计站点数目 COUNT(1) over (partition by RESULT.STATION_TYPE || RESULT.PROJECT_ID) TOTAL_COUNT, --根据水情站点类型和监测指标统计正常站点数目 --SUM(CASE WHEN RESULT.IF_GUARD_LEVEL = 'YES' THEN 1 ELSE 0 END) over (partition by RESULT.STATION_TYPE || RESULT.PROJECT_ID) NORMAL_COUNT, SUM(DECODE(RESULT.IF_GUARD_LEVEL,'YES',1,0)) over (partition by RESULT.STATION_TYPE || RESULT.PROJECT_ID) NORMAL_COUNT, --根据水情站点类型和监测指标统计异常站点数目 --SUM(CASE WHEN RESULT.IF_GUARD_LEVEL = 'YES' THEN 0 ELSE 1 END) over (partition by RESULT.STATION_TYPE || RESULT.PROJECT_ID) UNNORMAL_COUNT SUM(DECODE(RESULT.IF_GUARD_LEVEL,'YES',0,1)) over (partition by RESULT.STATION_TYPE || RESULT.PROJECT_ID) UNNORMAL_COUNT FROM ( --查询每个站点最新一条实时监控数据 SELECT * FROM ( SELECT R.SITE_ID, B."NAME" AS SITE_NAME, B.STATION_TYPE, DICT."NAME" AS STATION_NAME, TO_CHAR(R.MONITOR_TIME,'yyyy-MM-dd HH:mm') AS MONITOR_TIME, R.PROJECT_ID, D."NAME" AS PROJECT_NAME, R.MONITOR_RESULT, R.IF_GUARD_LEVEL, --查询同一个站点同一个指标的最新一条记录 ROW_NUMBER() OVER(PARTITION BY R.SITE_ID,R.PROJECT_ID ORDER BY R.MONITOR_TIME DESC) RN FROM T_WATER_REALTIME_RESULT R LEFT JOIN V_WATER_SITE_BASE_INFO B ON B."ID" = R.SITE_ID LEFT JOIN PLATFORM_DICTIONARY_DATA DICT ON DICT."VALUE" = B.STATION_TYPE AND DICT.CODE = 'WATER_TYPE' LEFT JOIN PLATFORM_DICTIONARY_DATA D ON R.PROJECT_ID = D."VALUE" AND D.CODE = 'WATER_PROJECT_TYPE' WHERE 1=1 AND B.IF_NEW = 'YES' ORDER BY R.MONITOR_TIME DESC,R.SITE_ID ) T WHERE T.RN = 1 ) RESULT ORDER BY RESULT.STATION_TYPE,RESULT.MONITOR_TIME DESC |
CopyRight 2018-2019 实验室设备网 版权所有 |